package com.esteel.settlement.dao.impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageImpl;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Pageable;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementSetter;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Component;
import org.springframework.util.ConcurrentReferenceHashMap;

import com.alibaba.druid.sql.PagerUtils;
import com.alibaba.druid.util.JdbcConstants;
import com.esteel.settlement.vo.BaseQueryVo;

/**
 * 
 * 基于JdbcTemplate 实现的查询分页数据的方法类
 * 
 * @author zhangxiuzhi
 *
 */
@Component
public class PageQuery {

	private Logger logger =  LoggerFactory.getLogger(this.getClass());

	//用于存储RowMapper的实现类 主要基于效率考虑
	private Map<Class<?>,RowMapper<?>> mapperCache = new ConcurrentReferenceHashMap<Class<?>,RowMapper<?>>();
	
	@Autowired
	private JdbcTemplate jdbcTemplate;

	
	/**
	 * 缓存对象类的rowmapper处理类
	 * @param voType
	 * @return
	 */
	public <T> RowMapper<T> getMapper(Class<T> voType){
		
		@SuppressWarnings("unchecked")
		RowMapper<T> mapper = (RowMapper<T>) mapperCache.get(voType);
		if (mapper!=null){
			return mapper;
		}
		
		BeanPropertyRowMapper<T> rowMapper = new BeanPropertyRowMapper<T>(voType);
		
		mapperCache.put(voType, rowMapper);
		
		return rowMapper;
	}
	
	/**
	 * 查询记录总条数的sql语句
	 * @param sql
	 * @return
	 */
	private String countSql(String sql){
		
		return  PagerUtils.count(sql, JdbcConstants.ORACLE);
		
//		StringBuilder sb = new StringBuilder("select count(*) as count from (");
//		sb.append(sql);
//		sb.append(")");
//		return sb.toString();
	}
	
	/**
	 * 返回总条数
	 * @param sql
	 * @return
	 */
	private long count(String sql){
		return  jdbcTemplate.queryForObject(this.countSql(sql), Long.class);
	}
	
	/**
	 * 根据给定的条件查询记录总条数
	 * @param sql
	 * @param pss
	 * @return
	 */
	private long count(String sql,PreparedStatementSetter pss){
		ResultSetExtractor<Long>  resultSetExtractor= new ResultSetExtractor<Long>(){
			@Override
			public Long extractData(ResultSet rs) throws SQLException, DataAccessException {
				return rs.getLong(1);
			}};
		return  jdbcTemplate.query(this.countSql(sql), pss,resultSetExtractor);
	}
	
	/**
	 * 根据给定的条件查询记录总条数
	 * @param sql
	 * @param pss
	 * @return
	 */
	private long count(String sql,Object[] args){
		return  jdbcTemplate.queryForObject(this.countSql(sql), args, Long.class);
	}
	
	/**
	 * 返回sql语句的分页sql
	 * @param sql
	 * @return
	 */
	private String pageSql(String sql,Pageable pageable){
	
		return PagerUtils.limit(sql,  JdbcConstants.ORACLE, pageable.getOffset(), pageable.getPageSize());
		
//		StringBuilder sb = new StringBuilder("SELECT *  FROM (SELECT row_.*, ROWNUM rownum_   FROM (");
//		sb.append(sql);
//		sb.append(") row_ where rownum <= ");
//		sb.append(pageable.getOffset()+pageable.getPageSize());
//		sb.append(") WHERE rownum_ > ");
//		sb.append(pageable.getOffset());
//		
//		return sb.toString();
	}
	
	/**
	 * 根据sql语句 查询给定的分页数据 并用给定的类型封装
	 * @param sql
	 * @param pageable
	 * @param voType
	 * @return
	 */
	public <T> Page<T> query(String sql,BaseQueryVo vo,Class<T> voType) {
		logger.info(sql);
		Pageable pageable = vo.getPageable();
		long total = count(sql);
		List<T> list = jdbcTemplate.query(pageSql(sql, pageable), getMapper(voType));
		Page<T> page = new PageImpl<T>(list, pageable, total);
		//返回页数超出总页数 则取最后一页的数据
		if (pageable.getOffset()>page.getTotalElements()){
			PageRequest newPageable = new  PageRequest(page.getTotalPages()-1,pageable.getPageSize());
			list = jdbcTemplate.query(pageSql(sql, newPageable), getMapper(voType));
			page = new PageImpl<T>(list, newPageable, total);
		}
		return page;
		
	}
	
	/**
	 * 根据sql语句 查询给定的分页数据 并用给定的类型封装
	 * @param sql
	 * @param pss 参数
	 * @param vo
	 * @param voType
	 * @return
	 */
	public <T> Page<T> query(String sql,PreparedStatementSetter pss,BaseQueryVo vo,Class<T> voType) {
		logger.info(sql);
		Pageable pageable = vo.getPageable();
		long total = count(sql,pss);
		List<T> list = jdbcTemplate.query(pageSql(sql, pageable), pss, getMapper(voType));
		Page<T> page = new PageImpl<T>(list, vo.getPageable(),total);
		//返回页数超出总页数 则取最后一页的数据
		if (pageable.getOffset()>page.getTotalElements()){
			PageRequest newPageable = new  PageRequest(page.getTotalPages()-1,pageable.getPageSize());
			list = jdbcTemplate.query(pageSql(sql, newPageable), pss, getMapper(voType));
			page = new PageImpl<T>(list, newPageable, total);
		}
		return page;
	}
	

	/**
	 * 根据sql语句 查询给定的分页数据 并用给定的类型封装
	 * @param sql
	 * @param args
	 * @param vo
	 * @param voType
	 * @return
	 */
	public <T> Page<T> query(String sql,Object[] args,BaseQueryVo vo,Class<T> voType){
		logger.info(sql);
		Pageable pageable = vo.getPageable();
		long total = count(sql,args);
		List<T> list = jdbcTemplate.query(pageSql(sql, pageable), args, getMapper(voType));
		Page<T> page = new PageImpl<T>(list, vo.getPageable(),total);
		//返回页数超出总页数 则取最后一页的数据
		if (pageable.getOffset()>page.getTotalElements()){
			PageRequest newPageable = new  PageRequest(page.getTotalPages()-1,pageable.getPageSize());
			list = jdbcTemplate.query(pageSql(sql, newPageable), args, getMapper(voType));
			page = new PageImpl<T>(list, newPageable, total);
		}
		return page;
	}
}
